package fast.loader;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement; 
import java.util.List;

public class DBController {
	
	private static final DBController dbcontroller = new DBController();
    private static Connection connection;
    private static final String DB_PATH = "./db/fast.db";
    
    static {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            System.err.println("Fehler beim Laden des JDBC-Treibers");
            e.printStackTrace();
        }
    }
    
    private DBController(){
    }
    
    public static DBController getInstance(){
        return dbcontroller;
    }
    
    public void initDBConnection() {
        try {
            if (connection != null)
                return;
            System.out.println("Creating Connection to Database...");
            connection = DriverManager.getConnection("jdbc:sqlite:" + DB_PATH);
            if (!connection.isClosed())
                System.out.println("...Connection established");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        Runtime.getRuntime().addShutdownHook(new Thread() {
            public void run() {
                try {
                    if (!connection.isClosed() && connection != null) {
                        connection.close();
                        if (connection.isClosed())
                            System.out.println("Connection to Database closed");
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        });
    }
    
    public void addItem(Item item){
    	 try {
             Statement stmt = connection.createStatement();
             stmt.executeUpdate("CREATE TABLE IF NOT EXISTS items (id, name, icon);");
             
             PreparedStatement ps = connection
                     .prepareStatement("INSERT INTO items VALUES (?, ?, ?);");

             ps.setInt(1, item.getId());
             ps.setString(2, item.getName());
             ps.setString(3, item.getIcon());
             ps.addBatch(); 
             
             connection.setAutoCommit(false);
             ps.executeBatch();
             connection.setAutoCommit(true); 
             
    	 } catch (SQLException e) {
             System.err.println("Couldn't handle DB-Query");
             e.printStackTrace();
         }
    }
    
    public List<Item> getItems(){
    	try {
    		Statement stmt = connection.createStatement();
    		ResultSet rs = stmt.executeQuery("SELECT * FROM items;");
            while (rs.next()) {
                System.out.println("id = " + rs.getInt("id"));
                System.out.println("name = " + rs.getString("name"));
                System.out.println("icon = " + rs.getString("icon"));
            }
            rs.close(); 
    	} catch (SQLException e) {
            System.err.println("Couldn't handle DB-Query");
            e.printStackTrace();
        }
		return null;
    }
}
